package arch.coal.ellipse.security.utils;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.SQLWarning;
import java.sql.Statement;
import java.util.Properties;

import org.apache.log4j.Logger;


/**
 * This class will attemp to make a JDBC connection to a Db and aid in
 * the access of data.  The class expects a Proprty file to be passed 
 * in which contains the driver and datasouce information, along with
 * the Db logon ID and Password.
 * @author mblackford
 * @version $Id:
 */
public class JDBCUtil {
	
	Properties _prop = null;
	Connection _con = null;
	static Logger logger = Logger.getLogger(JDBCUtil.class);

	// Constructor :  register the JDBC Driver with the Driver Manager.
	public JDBCUtil(Properties prop) throws ClassNotFoundException, SQLException 
	{
		_prop = prop;
		getConnected();
	}
	
	/**
	 * Attempts to connect to the Db based on the driver and data source 
	 * info contained in the Properties file.
	 * @throws ClassNotFoundException
	 * @throws SQLException
	 */
	private void getConnected() throws ClassNotFoundException, SQLException {
		try {
//			Class.forName( "sun.jdbc.odbc.JdbcOdbcDriver" );
			Class.forName(_prop.getProperty("db.driver"));			
		} catch (ClassNotFoundException cnfe) {
			logger.error("Couln't find the driver: [" + _prop.getProperty("db.driver") + "]");
			cnfe.printStackTrace();
			throw cnfe;
		}
		
		logger.debug("Registered the driver ok, so let's make a connection.");
		
		String dataSource = _prop.getProperty("db.dataSource");
		String userName = _prop.getProperty("db.userName");
		String passWord = _prop.getProperty("db.passWord");
		
		try {
			_con =
				DriverManager.getConnection(dataSource, userName, passWord);
		} catch (SQLException se) {
			logger.error("Couldn't connect !!!");
			logger.error("attempted to logon to [" + dataSource + ")"
					+ " as [" + userName + "] [" + passWord + "]");
			se.printStackTrace();
			throw se;
		}
		logger.info("Now connected to [" + dataSource + "]");
	}

	public void printWarnings(SQLWarning warn) {
		while (warn != null) {
			logger.warn("\nSQL Warning: ");
			logger.warn(warn.getMessage());
			logger.warn("ANSI-92 SQL State: " + warn.getSQLState());
			logger.warn("Vendor Error Code: " + warn.getErrorCode());
			warn = warn.getNextWarning();
		}
	}

	/**
	 * Obtains the requested data from the data base.
	 * @param sqlname
	 * @param fromProperty
	 * @return
	 * @throws SQLException
	 */
	public ResultSet getResultSet(String sqlname, boolean fromProperty) throws SQLException {
		
		String sql;
		if( fromProperty ) {
			sql = _prop.getProperty(sqlname);
		} else {
			sql = sqlname;
		}
		
		Statement stmt = createStatement();
		
		logger.debug("trying [" + sql + "]");
		ResultSet rs = stmt.executeQuery(sql);

		printWarnings(stmt.getWarnings());
		printWarnings(rs.getWarnings());
	
		return rs;
	}

	public Statement createStatement() throws SQLException {
		if (!_con.isClosed()) {
			return _con.createStatement(
				ResultSet.TYPE_SCROLL_INSENSITIVE,
				ResultSet.CONCUR_READ_ONLY);
		}
		else
			return null;
	}

/*	public PreparedStatement createPreparedStatement(String ps)
		throws SQLException {
		if (!_con.isClosed())
			return _con.prepareStatement(ps);
		else
			return null;
	}*/

	public void setAutoCommit(boolean ro) throws SQLException {
		if (!_con.isClosed()) {
			_con.setAutoCommit(ro);
		}
		return;
	}

	public void commit() throws SQLException {
		if (!_con.isClosed()) {
			_con.commit();
		}
		return;
	}

	/**
	 * Makes sure the data base connection is properlt closed.
	 * @throws SQLException
	 */
	public void close() throws SQLException {
		try {
			if (!_con.isClosed()) {
				logger.debug(" closing Connection");
				_con.close();
			}
		} catch (SQLException e) {
			throw e;
		}
		return;
	}

	public boolean isClosed() throws SQLException {
		try {
			return _con.isClosed();
		} catch (SQLException e) {
			throw e;
		}
	}

	/**
	 * Makes sure the data base connection is properlt closed.
	 * @throws SQLException
	 */
	protected void finalize() throws SQLException {
		if (!_con.isClosed()) {
			logger.debug(" closing Connection");
			_con.close();
		}
	}

	public String getParsedStmt(String sqlname, String[] values) {
		StringBuffer pattern = new StringBuffer("\'&0\'");
		StringBuffer temp = new StringBuffer(sqlname);

		for (int outer = 1; outer <= values.length; outer++) {
			int index = 0;
			pattern.replace(
				2,
				2 + (Integer.toString(outer)).length(),
				Integer.toString(outer));
			for (int inner = 0; index >= 0; inner++) {
				index = (temp.toString()).indexOf(pattern.toString(), index);
				if (index > 0) {
					temp.replace(index + 1, index + 3, values[outer - 1]);
				}
			}
		}
		return temp.toString();
	}

}
